﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.DB;
using PMPUtils.Resources;
using PMPUtils.Configuration;
using PMPUtils.Util;
using System.Net.Mail;

namespace PMPUtils.Goals
{
    public class GoalsOperator
    {
        public static String LEFT_OVER_GOAL = "Left_Over";

        public static void InitializeGoalsGridView(GridView grvGoals)
        {
            grvGoals.Columns.Clear();

            if (grvGoals.Columns.Count == 0)
            {
                // Add column
                ButtonField pickColumn = new ButtonField();
                pickColumn.ButtonType = ButtonType.Link;
                pickColumn.CommandName = "ManageGoals";
                pickColumn.Text = "Manage";

                BoundField Goal = new BoundField();
                Goal.HeaderText = "Goal";
                Goal.DataField = "goal";

                BoundField progress = new BoundField();
                progress.HeaderText = "Progress";
                progress.DataField = "progress";

                BoundField Task = new BoundField();
                Task.HeaderText = "Task";
                Task.DataField = "task_id";

                BoundField team = new BoundField();
                team.HeaderText = "Team";
                team.DataField = "team";

                BoundField status = new BoundField();
                status.HeaderText = "Status";
                status.DataField = "status";

                BoundField owner = new BoundField();
                owner.HeaderText = "Owner";
                owner.DataField = "owner";

                BoundField due_date = new BoundField();
                due_date.HeaderText = "Due Date";
                due_date.DataField = "due_date";

                BoundField est_due_date = new BoundField();
                est_due_date.HeaderText = "Expected Due Date";
                est_due_date.DataField = "est_due_date";

                BoundField orig_est = new BoundField();
                orig_est.HeaderText = "Orig";
                orig_est.DataField = "orig_est";
                orig_est.DataFormatString = "{0:F0}";

                BoundField actual = new BoundField();
                actual.HeaderText = "Actual";
                actual.DataField = "actual";
                actual.DataFormatString = "{0:F0}";

                BoundField rem_est = new BoundField();
                rem_est.HeaderText = "Rem";
                rem_est.DataField = "rem_est";
                rem_est.DataFormatString = "{0:F0}";

                BoundField ETC = new BoundField();
                ETC.HeaderText = "ETC";
                ETC.DataField = "ETC";
                ETC.DataFormatString = "{0:F0}";

                BoundField EAC = new BoundField();
                EAC.HeaderText = "EAC";
                EAC.DataField = "EAC";
                EAC.DataFormatString = "{0:F0}";

                BoundField variance = new BoundField();
                variance.HeaderText = "Variance";
                variance.DataField = "variance";
                variance.DataFormatString = "{0:P0}";

                BoundField priority = new BoundField();
                priority.HeaderText = "Priority";
                priority.DataField = "priority";

                BoundField idColumn = new BoundField();
                idColumn.HeaderText = "ID";
                idColumn.DataField = "id";

                grvGoals.Columns.Add(pickColumn);
                grvGoals.Columns.Add(Goal);
                grvGoals.Columns.Add(progress);
                grvGoals.Columns.Add(Task);
                grvGoals.Columns.Add(status);
                grvGoals.Columns.Add(team);
                grvGoals.Columns.Add(owner);
                grvGoals.Columns.Add(due_date);
                grvGoals.Columns.Add(priority);
                grvGoals.Columns.Add(est_due_date);
                grvGoals.Columns.Add(orig_est);
                grvGoals.Columns.Add(actual);
                grvGoals.Columns.Add(rem_est);
                grvGoals.Columns.Add(ETC);
                grvGoals.Columns.Add(EAC);
                grvGoals.Columns.Add(variance);
                grvGoals.Columns.Add(idColumn);

                String currentSprint = SprintOperator.ReadCurrentSprint();

                // List Data
                String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where 1 = 2 and sprint = " + currentSprint + "";
                DBOperator localDBOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                DataTable dtGoals = localDBOperator.Read(strQuery);

                grvGoals.DataSource = dtGoals.DefaultView;
                grvGoals.DataBind();
            }
        }

        /// <summary>
        /// Add left over stories
        /// </summary>
        public static void AddLeftOverTasks()
        {
            // Grab the last sprint which has not been generated left over tasks yet.
            String strQueryLastSprint = "select id from sprint where iscompleted = 1 and (isleftovercreated = 0 or isleftovercreated is null) order by sprint desc";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtLastSprint = localOperator.Read(strQueryLastSprint);

            String lastSprintID = String.Empty;
            foreach (DataRow row in dtLastSprint.Rows)
            {
                if (row["id"] != DBNull.Value)
                {
                    lastSprintID = row["id"].ToString();
                }
            }

            if (lastSprintID.Equals(String.Empty))
            {
                return;
            }

            // Query left over tasks
            String strQueryLeftOverGoals = " select goals.team, sum(goal_dtl.orig_est) as orig_est " +
                " from goals " +
                " join goal_dtl on (goal_dtl.parent_id = goals.id) " +
                " where goal_dtl.status <> 'Complete' " +
                " and sprint = '" + lastSprintID + "' and goal_dtl.rev is null" +
                " group by goals.team";
            localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtLeftOverTasks = localOperator.Read(strQueryLeftOverGoals);

            foreach (DataRow row in dtLeftOverTasks.Rows)
            {
                if (row["team"] != DBNull.Value)
                {
                    String team = row["team"].ToString();
                    String origEst = "" + (row["orig_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(row["orig_est"]));
                    String leftOverGoalID = ReadLeftOverGoalID(team);

                    if (!leftOverGoalID.Equals(String.Empty))
                    {
                        continue;
                    }

                    // Add the left over goal
                    String currentSprintID = SprintOperator.ReadCurrentSprint();
                    String goalOwner = "13"; // NF2
                    if(team.Equals("NF1")) { goalOwner = "12"; } // NF1

                    DateTime sprintStart = SprintOperator.ReadSprintStart(currentSprintID);
                    sprintStart = sprintStart.AddDays(5);
                    String dueDate = sprintStart.ToString("yyyy-MM-dd");

                    AddNewGoal(LEFT_OVER_GOAL, "", goalOwner, "11", dueDate, "1", origEst, currentSprintID);

                    // Add left over goal details
                    String strQueryLeftOverGoalDetails = " select distinct goals.team, goal_dtl.owner, goal_dtl.task_id, goal_dtl.orig_est, goal_dtl.due_date " +
                        " from goals " +
                        " join goal_dtl on (goal_dtl.parent_id = goals.id) " +
                        " where goal_dtl.status <> 'Complete' " +
                        " and sprint = '" + lastSprintID + "' and goal_dtl.rev is null and goals.team = '" + team + "'";
                    localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    DataTable dtLeftOverDetails = localOperator.Read(strQueryLeftOverGoalDetails);

                    foreach (DataRow detailRow in dtLeftOverDetails.Rows)
                    {
                        if(detailRow["task_id"] != DBNull.Value) {
                            String task = detailRow["task_id"].ToString();
                            String owner = detailRow["owner"].ToString();
                            String due_date = detailRow["due_date"].ToString();
                            String detailOrigEst = "" + (detailRow["orig_est"] == DBNull.Value ? 0.00 : Convert.ToDouble(detailRow["orig_est"]));

                            leftOverGoalID = ReadLeftOverGoalID(team);

                            GoalDetailsOperator.AddNewGoalDetail(leftOverGoalID, task, owner, due_date);
                        }
                    }
                }
            }

            // Update last sprint to left over created
            String strUpdateLastSprint = "update sprint set isleftovercreated = 1 where id = " + lastSprintID;
            localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            localOperator.Update(strUpdateLastSprint);
        }

        public static String ReadLeftOverGoalID(String team)
        {
            String currentSprintID = SprintOperator.ReadCurrentSprint();
            String strQueryLeftOverCreated = "select id from goals where team = '" + team + "' and sprint = " + currentSprintID + " and goal = '" + LEFT_OVER_GOAL + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtLeftOverCreated = localOperator.Read(strQueryLeftOverCreated);

            String leftOverGoalID = String.Empty;
            foreach (DataRow row in dtLeftOverCreated.Rows)
            {
                if (row["id"] != DBNull.Value)
                {
                    leftOverGoalID = row["id"].ToString();
                }
            }

            return leftOverGoalID;
        }

        /// <summary>
        /// Add a goal
        /// </summary>
        /// <param name="goal">Goal description</param>
        /// <param name="taskID">Actual task ID</param>
        /// <param name="owner">User ID</param>
        /// <param name="status">Status ID</param>
        /// <param name="dueDate">Due date of the goal</param>
        /// <param name="priority">Priority of the goal</param>
        /// <param name="sprint">The sprint of the task planned.</param>
        public static void AddNewGoal(String goal, String taskID, String owner,
            String progress, String dueDate, String priority, String origEstimation, String sprint)
        {
            String team = ResourceOperator.ReadTeam(owner);

            String strInsert = "insert into goals (goal, task_id, owner, progress, due_date, priority, team, sprint) values " +
                " ('" + goal + "', '" + taskID + "', " + owner + ", " + progress + ", '" + dueDate + "', " + priority + ", '" + team + "', " + sprint + ")";

            if (!origEstimation.Equals(String.Empty))
            {
                strInsert = "insert into goals (goal, task_id, owner, progress, due_date, priority, orig_est, team, sprint) values " +
                " ('" + goal + "', '" + taskID + "', " + owner + ", " + progress + ", '" + dueDate + "', " + priority + ", " + origEstimation + ", '" + team + "', " + sprint + ")";
            }

            DBOperator localDBOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            localDBOperator.Update(strInsert);
        }

        /// <summary>
        /// Refresh the grid view of the goals
        /// </summary>
        /// <param name="grvGoals"></param>
        public static void RefreshGoalsGridView(GridView grvGoals, String sprint)
        {
            DataTable dtCurrentGoals = ReadSprintGoals(sprint);

            grvGoals.DataSource = dtCurrentGoals.DefaultView;
            grvGoals.DataBind();
        }

        /// <summary>
        /// Read all the current sprint goals
        /// </summary>
        /// <returns></returns>
        //public static DataTable ReadCurrentSprintGoals()
        //{
        //    String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
        //               " from goals " +
        //               " join usr on (usr.id = goals.owner) " +
        //               " join goalstatus on (goalstatus.id = goals.progress) " +
        //               " join goalpriority on (goalpriority.id = goals.priority) " +
        //               "  where rev is null and sprint = 'CURRENT'" +
        //               "  order by goals.team, goals.priority, goal";

        //    DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
        //    DataTable dtCurrentGoals = dbOperator.Read(strQuery);

        //    return dtCurrentGoals;
        //}

        public static DataTable ReadSprintGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " left outer join usr on (usr.id = goals.owner) " +
                       " left outer join goalstatus on (goalstatus.id = goals.progress) " +
                       " left outer join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCurrentGoals = dbOperator.Read(strQuery);

            return dtCurrentGoals;
        }

        public static String ReadPriorityClause(String sprint)
        {
            String caluse = String.Empty;
            caluse += " case ";

            // Read priority and task IDs
            String strQuery = "select distinct goal_dtl.task_id JIRA, goalpriority.name Priority " +
                " from goals " +
                " join goal_dtl on (goal_dtl.parent_id = goals.id) " +
                " join goalpriority on (goalpriority.id = goals.priority) " +
                " where goals.rev is null " +
                " order by priority";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtNF1HighPriorityGoals = dbOperator.Read(strQuery);
            foreach (DataRow row in dtNF1HighPriorityGoals.Rows)
            {
                if (row["JIRA"] != DBNull.Value)
                {
                    String priority = row["Priority"].ToString();

                    // Just for sorting
                    if (priority.Equals("LOW"))
                    {
                        caluse += " when jiraissue.pkey = '" + row["JIRA"].ToString() + "' then 'nLOW' ";
                    }
                    else
                    {
                        caluse += " when jiraissue.pkey = '" + row["JIRA"].ToString() + "' then '" + priority + "' ";
                    }
                }
            }

            // Read priority and story
            String strQueryGoals = " select task_id JIRA, goalpriority.name Priority " +
                " from goals " +
                " join goalpriority on (goalpriority.id = goals.priority) " +
                " where goals.rev is null " +
                " and sprint = 5 and (task_id <> null or task_id <> '')";
            
            dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtStoryPriorities = dbOperator.Read(strQueryGoals);
            foreach (DataRow row in dtStoryPriorities.Rows)
            {
                if (row["JIRA"] != DBNull.Value)
                {
                    String priority = row["Priority"].ToString();

                    // Just for sorting
                    if (priority.Equals("LOW"))
                    {
                        caluse += " when jiraissue.pkey = '" + row["JIRA"].ToString() + "' then 'nLOW' ";
                    }
                    else
                    {
                        caluse += " when jiraissue.pkey = '" + row["JIRA"].ToString() + "' then '" + priority + "' ";
                    }
                }
            }

            caluse += " else 'Not Current Sprint' end ";

            return caluse;
        }

        public static DataTable ReadNF1HighPriorityGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "    and team = 'NF1' and goalpriority.id = 1" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtGoals = dbOperator.Read(strQuery);

            return dtGoals;
        }

        public static DataTable ReadNF1MediumPriorityGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "    and team = 'NF1' and goalpriority.id = 2" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtGoals = dbOperator.Read(strQuery);

            return dtGoals;
        }

        public static DataTable ReadNF1LowPriorityGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "    and team = 'NF1' and goalpriority.id = 3" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtGoals = dbOperator.Read(strQuery);

            return dtGoals;
        }

        public static DataTable ReadNF2HighPriorityGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "    and team = 'NF2' and goalpriority.id = 1" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtGoals = dbOperator.Read(strQuery);

            return dtGoals;
        }

        public static DataTable ReadNF2MediumPriorityGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "    and team = 'NF2' and goalpriority.id = 2" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtGoals = dbOperator.Read(strQuery);

            return dtGoals;
        }

        public static DataTable ReadNF2LowPriorityGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + "" +
                       "    and team = 'NF2' and goalpriority.id = 3" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtGoals = dbOperator.Read(strQuery);

            return dtGoals;
        }

        /// <summary>
        /// Read all the current sprint goals which are not completed yet
        /// </summary>
        /// <returns></returns>
        //public static DataTable ReadCurrentSprintInProgressGoals()
        //{
        //    String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
        //               " from goals " +
        //               " join usr on (usr.id = goals.owner) " +
        //               " join goalstatus on (goalstatus.id = goals.progress) " +
        //               " join goalpriority on (goalpriority.id = goals.priority) " +
        //               "  where rev is null and sprint = 'CURRENT' and (status <> 'Complete' or (status is null or status = ''))" +
        //               "  order by goals.team, goals.priority, goal";

        //    DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
        //    DataTable dtCurrentGoals = dbOperator.Read(strQuery);

        //    return dtCurrentGoals;
        //}

        public static DataTable ReadInProgressGoals(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + " and (status <> 'Complete' or (status is null or status = ''))" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCurrentGoals = dbOperator.Read(strQuery);

            return dtCurrentGoals;
        }

        /// <summary>
        /// Read the goals without linked to a specific task
        /// </summary>
        /// <returns></returns>
        //public static DataTable ReadCurrentSprintGoalsWithoutSpecificTask()
        //{
        //    String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
        //               " from goals " +
        //               " join usr on (usr.id = goals.owner) " +
        //               " join goalstatus on (goalstatus.id = goals.progress) " +
        //               " join goalpriority on (goalpriority.id = goals.priority) " +
        //               "  where rev is null and sprint = 'CURRENT' and (task_id is null or task_id = '')" +
        //               "  order by goals.team, goals.priority, goal";

        //    DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
        //    DataTable dtCurrentGoals = dbOperator.Read(strQuery);

        //    return dtCurrentGoals;
        //}

        /// <summary>
        /// Read the goal description
        /// </summary>
        /// <param name="goalID"></param>
        /// <returns></returns>
        public static String ReadGoalDesc(String goalID)
        {
            String goalDesc = String.Empty;

            String strQuery = "select goal from goals where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtSummary = dbOperator.Read(strQuery);

            foreach (DataRow row in dtSummary.Rows)
            {
                if (row["goal"] != DBNull.Value)
                {
                    goalDesc = row["goal"].ToString();

                    break;
                }
            }

            return goalDesc;
        }

        /// <summary>
        /// Read specific column value from the DB.
        /// </summary>
        /// <param name="goalID"></param>
        /// <param name="columnName"></param>
        /// <returns>String</returns>
        public static String ReadGoalAttributes(String goalID, String columnName)
        {
            String returnString = String.Empty;

            String strQuery = "select " + columnName + " from goals where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtSummary = dbOperator.Read(strQuery);

            foreach (DataRow row in dtSummary.Rows)
            {
                if (row[columnName] != DBNull.Value)
                {
                    returnString = row[columnName].ToString();

                    break;
                }
            }

            return returnString;
        }

        /// <summary>
        /// Read the owner's email
        /// </summary>
        /// <param name="goalID"></param>
        /// <returns></returns>
        public static String ReadGoalEmail(String goalID)
        {
            String email = String.Empty;

            String strQuery = "select usr.email from goals join usr on (usr.id = goals.owner) where goals.id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtSummary = dbOperator.Read(strQuery);

            foreach (DataRow row in dtSummary.Rows)
            {
                if (row["email"] != DBNull.Value)
                {
                    email = row["email"].ToString();

                    break;
                }
            }

            return email;
        }

        public static DataTable ReadGoalsWithoutSpecificTask(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + " and (task_id is null or task_id = '')" +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCurrentGoals = dbOperator.Read(strQuery);

            return dtCurrentGoals;
        }

        public static String ReadGoalOwner(String goalID)
        {
            String strQuery = "select owner from goals where id = " + goalID;

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCurrentGoals = dbOperator.Read(strQuery);

            String owner = String.Empty;
            foreach (DataRow row in dtCurrentGoals.Rows)
            {
                if (row["owner"] != DBNull.Value)
                {
                    owner = row["owner"].ToString().Trim();
                }
            }

            return owner;
        }

        /// <summary>
        /// Read the goals with specific tasks
        /// </summary>
        /// <returns></returns>
        //public static DataTable ReadCurrentSprintGoalsWithSpecificTask()
        //{
        //    String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
        //               " from goals " +
        //               " join usr on (usr.id = goals.owner) " +
        //               " join goalstatus on (goalstatus.id = goals.progress) " +
        //               " join goalpriority on (goalpriority.id = goals.priority) " +
        //               "  where rev is null and sprint = 'CURRENT' and (task_id <> null or task_id <> '') " +
        //               "  order by goals.team, goals.priority, goal";

        //    DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
        //    DataTable dtCurrentGoals = dbOperator.Read(strQuery);

        //    return dtCurrentGoals;
        //}

        public static DataTable ReadGoalsWithSpecificTask(String sprint)
        {
            String strQuery = " select goal, goalstatus.name progress, task_id, status, goals.team, usr.usr_name as owner, due_date, goalpriority.name priority, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goals.id " +
                       " from goals " +
                       " join usr on (usr.id = goals.owner) " +
                       " join goalstatus on (goalstatus.id = goals.progress) " +
                       " join goalpriority on (goalpriority.id = goals.priority) " +
                       "  where rev is null and sprint = " + sprint + " and (task_id <> null or task_id <> '') " +
                       "  order by goals.team, goals.priority, goal";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCurrentGoals = dbOperator.Read(strQuery);

            return dtCurrentGoals;
        }

        /// <summary>
        /// Read the tips from the local database: (status) summary
        /// </summary>
        /// <param name="goalID"></param>
        /// <returns></returns>
        public static String ReadTips(String goalID)
        {
            String tip = String.Empty;

            String strQuery = "select task_desc, status from goals where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtSummary = dbOperator.Read(strQuery);

            foreach (DataRow row in dtSummary.Rows)
            {
                if (row["task_desc"] != DBNull.Value)
                {
                    String status = row["status"].ToString();
                    String summary = row["task_desc"].ToString();

                    tip = "( " + status + " ) " + summary;
                    break;
                }
            }

            return tip;
        }

        /// <summary>
        /// True if the goal already added in the goals
        /// </summary>
        /// <param name="goal"></param>
        /// <returns></returns>
        public static bool IsGoalExists(String goal, String sprint)
        {
            String strQuery = "select COUNT(goal) exist from goals where goal = '" + goal + "' and sprint = " + sprint + " and rev is null";
            DataTable dtGoalExist = new DataTable();
            bool exists = false;
            
            DBOperator dbOpeartor = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dtGoalExist = dbOpeartor.Read(strQuery);

            foreach (DataRow row in dtGoalExist.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        exists = true;
                    }
                }
            }

            return exists;
        }

        /// <summary>
        /// Remove the specified goal and its details.
        /// </summary>
        /// <param name="goalID">The ID of the goal.</param>
        public static void RemoveGoal(String goalID)
        {
            String strDelete = "delete from goals where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strDelete);

            GoalDetailsOperator.RemoveAllDetails(goalID);
        }

        /// <summary>
        /// Switch the owner of the goal
        /// </summary>
        /// <param name="goalID">ID of the goal</param>
        /// <param name="newOwner">User ID of the new owner</param>
        public static void SwitchOwner(String goalID, String newOwner)
        {
            String strUpdate = "update goals set owner = " + newOwner + " where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the priority of the goal.
        /// </summary>
        /// <param name="goalID">The ID of the goal.</param>
        /// <param name="newPriority">The ID of the priority.</param>
        public static void ChangePriority(String goalID, String newPriority)
        {
            String strUpdate = "update goals set priority = " + newPriority + " where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the status of the goal
        /// </summary>
        /// <param name="goalID">ID of the goal.</param>
        /// <param name="newProgress">ID of the progress.</param>
        public static void ChangeStatus(String goalID, String newProgress)
        {
            String strUpdate = "update goals set progress = " + newProgress + " where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the original estimation for the dummy goal.
        /// </summary>
        /// <param name="goalID">Id of the goal.</param>
        /// <param name="newEstimation">The estimations in hours.</param>
        public static void ChangeEstimation(String goalID, String newEstimation)
        {
            String strUpdate = "update goals set orig_est = '" + newEstimation + "' where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the due date of the goal.
        /// </summary>
        /// <param name="goalID">ID of the goal.</param>
        /// <param name="newDueDate">New due date - 2011-08-11</param>
        public static void ChangeDueDate(String goalID, String newDueDate)
        {
            String strUpdate = "update goals set due_date = '" + newDueDate + "' where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        public static void ChangeGoalDescription(String goalID, String newDescription)
        {
            String strUpdate = "update goals set goal = '" + newDescription + "' where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the expected due date of the goal.
        /// </summary>
        /// <param name="goalID">ID of the goal.</param>
        /// <param name="newExpectedDueDate">New due date - 2011-08-11</param>
        public static void ChangeExpectedDueDate(String goalID, String newExpectedDueDate)
        {
            String strUpdate = "update goals set est_due_date = '" + newExpectedDueDate + "' where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the sprint of the goal
        /// </summary>
        /// <param name="goalID"></param>
        /// <param name="newSprint"></param>
        public static void ChangeSprint(String goalID, String newSprint)
        {
            String strUpdate = "update goals set sprint = '" + newSprint + "' where id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Whether or not already backed up today.
        /// </summary>
        /// <param name="sprint"></param>
        /// <returns></returns>
        public static bool IsBackedUpAlready(String sprint)
        {
            String strQuery = "select count(id) exist from goals where sprint = '" + sprint + "' and   rev = convert(varchar(50), year(dateadd(day, -1, getdate()))) + '-' + convert(varchar(50), month(dateadd(day, -1, getdate()))) + '-' + convert(varchar(50), day(dateadd(day, -1, getdate())))";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dt = dbOperator.Read(strQuery);

            bool isAlreadyBackedup = false;
            foreach (DataRow row in dt.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isAlreadyBackedup = true;
                        break;
                    }
                }
            }

            return isAlreadyBackedup;
        }

        /// <summary>
        /// Backup goals and details to yesterday
        /// </summary>
        public static void Backup()
        {
            String strBackupGoals = "insert into goals " +
                " SELECT [goal] " +
                " ,[task_id] " +
                " ,[owner] " +
                " ,[progress] " +
                " ,[priority] " +
                " ,[due_date] " +
                " ,[est_due_date] " +
                " ,[orig_est] " +
                " ,[rem_est] " +
                " ,[ETC] " +
                " ,[EAC] " +
                " ,[variance] " +
                " ,convert(varchar(50), year(dateadd(day, -1, getdate()))) + '-' + convert(varchar(50), month(dateadd(day, -1, getdate()))) + '-' + convert(varchar(50), day(dateadd(day, -1, getdate()))) as [rev] " +
                " ,[team] " +
                " ,[sprint] " +
                " ,[task_desc] " +
                " ,[status] " +
                " ,[actual] " +
                " FROM [goals] " +
                " where rev is null";
            DBOperator goalsOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            goalsOperator.Update(strBackupGoals);

            String strBackUpGoalsDetails = "insert into goal_dtl ([parent_id]  ,[task_id]  ,[task_desc]  ,[owner]  ,[due_date]  ,[est_due_date]  ,[orig_est]  ,[rem_est]  ,[ETC]  ,[EAC]  ,[variance]  ,[status]  ,[actual]  , [rev]) " +
                " SELECT [parent_id] " +
                " ,[task_id] " +
                " ,[task_desc] " +
                " ,[owner] " +
                " ,[due_date] " +
                " ,[est_due_date] " +
                " ,[orig_est] " +
                " ,[rem_est] " +
                " ,[ETC] " +
                " ,[EAC] " +
                " ,[variance] " +
                " ,[status] " +
                " ,[actual] " +
                " ,convert(varchar(50), year(dateadd(day, -1, getdate()))) + '-' + convert(varchar(50), month(dateadd(day, -1, getdate()))) + '-' + convert(varchar(50), day(dateadd(day, -1, getdate()))) as [rev] " +
                " FROM [goal_dtl] " +
                " where rev is null";
            DBOperator goalDetailsOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            goalDetailsOperator.Update(strBackUpGoalsDetails);
        }

        public static void SendUpdateMessage(String goalID, String subject, String userName)
        {
            MailAddressCollection sendTos = ResourceOperator.ReadAllRelativeEmailsForUpdate(goalID);
            String messageBody = "Check details on http://bs-pc-664:9090/goals/Dashboard.aspx";

            MailAddress mailFrom = ResourceOperator.GetEmailAddress(userName);

            EmailOperator.Send(subject, messageBody, sendTos, mailFrom, true, null);
        }
    }
}
